﻿using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Collections.Generic;
using zdzh.zdmgr.COL;
using zdzh.zdmgr.SFL;

namespace zdzh.zdmgr.DAL.OleDb
{
    /// <summary>
    /// 对象名称：回迁协议OleDb数据访问子类（数据访问层）
    /// 对象说明：提供“回迁协议类（业务逻辑层）”针对OleDb的“增删改查”等各种数据访问方法，继承通用数据访问父类。
    /// 调用说明：通常不需要直接实例化本类，而使用“回迁协议类（业务逻辑层）”中的DataAccess属性来调用本类所实现的方法。
    /// 作者姓名：飞翔熊
    /// 编写日期：2012-12-3 13:23:04
    /// </summary>
    public class FangyuanDAL : DAL.Common.FangyuanDAL
    {
        #region EasyCode所生成的默认代码
        //﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉
        //  此区域的代码为EasyCode所自动生成，实现了父类中定义的抽象方法。请不要直接修改该区域中的任何代码，   
        //  或在该区域中添加任何自定义代码，当该类发生变更时，您可以随时使用EasyCode重新生成覆盖其中的代码。  
        //﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍


        /// <summary>
        /// 将回迁协议（Huiqianxieyi）数据，采用INSERT操作插入到数据库中，并返回受影响的行数。
        /// </summary>
        /// <param name="huiqianxieyi">回迁协议（Huiqianxieyi）实例对象</param>
        public override int Insert(Fangyuan fangyuan)
        {
            int index = SFL.OleDbHelper.GetMaxIndex("fangyuan")+1;
            string sqlText = "INSERT INTO [fangyuan]"
                           + "([index],[type],[build],[unit],[floor],[room],[area],[designarea],[state],[price],[xieyiindex])"
                           + "VALUES"
                           + "(@index,@type,@build,@unit,@floor,@room,@area,@designarea,@state,@price,@xieyiindex)";
            OleDbParameter[] parameters = 
            {
                new OleDbParameter("@index"     , OleDbType.Integer , 4){ Value = index },
                new OleDbParameter("@type"      , OleDbType.Integer , 50){ Value = fangyuan.Type      },
                new OleDbParameter("@build"     , OleDbType.VarWChar , 50){ Value = fangyuan.Build       },
                new OleDbParameter("@unit"      , OleDbType.VarWChar , 50){ Value = fangyuan.Unit        },
                new OleDbParameter("@floor"     , OleDbType.VarWChar , 50){ Value = fangyuan.Floor },
                new OleDbParameter("@room"      , OleDbType.VarWChar , 50){ Value = fangyuan.Room },
                new OleDbParameter("@area"      , OleDbType.Decimal){ Value = fangyuan.Area },
                new OleDbParameter("@designarea", OleDbType.Decimal){ Value = fangyuan.Designarea },
                new OleDbParameter("@state"     , OleDbType.Integer , 4){ Value = fangyuan.State },
                new OleDbParameter("@price"     , OleDbType.Decimal){ Value = fangyuan.Price },
                new OleDbParameter("@xieyiindex"     , OleDbType.Integer , 4){ Value = fangyuan.XieyiIndex }
            };
            SFL.OleDbHelper.ExecuteNonQuery(sqlText, parameters);
            return index;
        }


        /// <summary>
        /// 将回迁协议（Huiqianxieyi）数据，根据主键“协议号（Xieyihao）”采用UPDATE操作更新到数据库中，并返回受影响的行数。
        /// </summary>
        /// <param name="huiqianxieyi">回迁协议（Huiqianxieyi）实例对象</param>
        public override int Update(Fangyuan fangyuan)
        {
            string sqlText = "UPDATE [fangyuan] SET "
                           + "[type]=@type,[build]=@build,[unit]=@unit,[floor]=@floor,[room]=@room,[designarea]=@designarea,[area]=@area,[state]=@state,[price]=@price,[xieyiindex]=@xieyiindex "
                           + "WHERE [index]=@index";
            OleDbParameter[] parameters = 
            {
                new OleDbParameter("@type"      , OleDbType.Integer , 50){ Value = fangyuan.Type      },
                new OleDbParameter("@build"     , OleDbType.VarWChar , 50){ Value = fangyuan.Build       },
                new OleDbParameter("@unit"      , OleDbType.VarWChar , 50){ Value = fangyuan.Unit        },
                new OleDbParameter("@floor"     , OleDbType.VarWChar , 50){ Value = fangyuan.Floor },
                new OleDbParameter("@room"      , OleDbType.VarWChar , 50){ Value = fangyuan.Room },
                new OleDbParameter("@designarea", OleDbType.Decimal){ Value = fangyuan.Designarea },
                new OleDbParameter("@area"      , OleDbType.Decimal){ Value = fangyuan.Area },
                new OleDbParameter("@state"     , OleDbType.Integer , 4){ Value = fangyuan.State },
                new OleDbParameter("@price"     , OleDbType.Decimal){ Value = fangyuan.Price },
                new OleDbParameter("@xieyiindex"     , OleDbType.Integer,4){ Value = fangyuan.XieyiIndex },
                new OleDbParameter("@index"     , OleDbType.Integer , 4){ Value = fangyuan.Index }
            };
            return SFL.OleDbHelper.ExecuteNonQuery(sqlText, parameters);
        }


        /// <summary>
        /// 根据回迁协议（Huiqianxieyi）的主键“协议号（Xieyihao）”采用DELETE操作从数据库中删除相关记录，并返回受影响的行数。
        /// </summary>
        /// <param name="xieyihao">回迁协议（Huiqianxieyi）的主键“协议号（Xieyihao）”</param>
        public override int Delete(int index)
        {
            string sqlText = "DELETE FROM [fangyuan] "
                           + "WHERE [index]=@index";
            OleDbParameter[] parameters = 
            {
                new OleDbParameter("@index" , OleDbType.Integer , 4){ Value = index }
            };
            return SFL.OleDbHelper.ExecuteNonQuery(sqlText, parameters);
        }


        /// <summary>
        /// 根据回迁协议（Huiqianxieyi）的主键“协议号（Xieyihao）”从数据库中获取回迁协议（Huiqianxieyi）的实例。
        /// 成功从数据库中取得记录返回新回迁协议（Huiqianxieyi）的实例“，没有取到记录返回null值。
        /// </summary>
        /// <param name="xieyihao">回迁协议（Huiqianxieyi）的主键“协议号（Xieyihao）”</param>
        public override Fangyuan GetDataByIndex(int index)
        {
            Fangyuan fangyuan = null;
            string sqlText = "SELECT * "
                           + "FROM [fangyuan] "
                           + "WHERE [index]=@index";
            OleDbParameter[] parameters = 
            {
                new OleDbParameter("@index" , OleDbType.Integer , 4){ Value = index }
            };

            OleDbDataReader oleDbDataReader = SFL.OleDbHelper.ExecuteReader(sqlText, parameters);
            if (oleDbDataReader.Read())
            {
                fangyuan = new Fangyuan();
                ReadFangyuanAllData(oleDbDataReader, fangyuan);
            }
            oleDbDataReader.Close();
            return fangyuan;
        }

        /// <summary>
        /// 通过协议索引获取房源
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public override List<Fangyuan> GetDataByXieyiIndex(int index)
        {
            string sqlText = "SELECT * "
                           + "FROM [fangyuan] "
                           + "WHERE [xieyiindex]=@index";
            OleDbParameter[] parameters = 
            {
                new OleDbParameter("@index" , OleDbType.Integer , 4){ Value = index }
            };

            List<Fangyuan> list = new List<Fangyuan>();
            OleDbDataReader oleDbDataReader = SFL.OleDbHelper.ExecuteReader(sqlText, parameters);
            while (oleDbDataReader.Read())
            {
                Fangyuan fangyuan = new Fangyuan();
                ReadFangyuanAllData(oleDbDataReader, fangyuan);
                list.Add(fangyuan);
            }
            oleDbDataReader.Close();
            return list;
        }

        /// <summary>
        /// 获取空闲房源
        /// </summary>
        /// <returns></returns>
        public override List<Fangyuan> GetDataByType(int type)
        {
            string sqlText = "SELECT * "
                           + "FROM [fangyuan] "
                           + "WHERE [type]=@type";
            OleDbParameter[] parameters = 
            {
                new OleDbParameter("@type" , OleDbType.Integer , 4){ Value = type }
            };

            List<Fangyuan> list = new List<Fangyuan>();
            OleDbDataReader oleDbDataReader = SFL.OleDbHelper.ExecuteReader(sqlText, parameters);
            while (oleDbDataReader.Read())
            {
                Fangyuan fangyuan = new Fangyuan();
                ReadFangyuanAllData(oleDbDataReader, fangyuan);
                list.Add(fangyuan);
            }
            oleDbDataReader.Close();
            return list;
        }
        public override List<Fangyuan> GetDataByType(int type, int state)
        {
            string sqlText = "SELECT * "
                           + "FROM [fangyuan] "
                           + "WHERE [type]=@type"
                           + " AND [state]=@state";
            OleDbParameter[] parameters = 
            {
                new OleDbParameter("@type" , OleDbType.Integer , 4){ Value = type },
                new OleDbParameter("@state" , OleDbType.Integer , 4){ Value = state }
            };

            List<Fangyuan> list = new List<Fangyuan>();
            OleDbDataReader oleDbDataReader = SFL.OleDbHelper.ExecuteReader(sqlText, parameters);
            while (oleDbDataReader.Read())
            {
                Fangyuan fangyuan = new Fangyuan();
                ReadFangyuanAllData(oleDbDataReader, fangyuan);
                list.Add(fangyuan);
            }
            oleDbDataReader.Close();
            return list;
        }

#region 房源列表
        public override List<string> GetBuildList()
        {
            string sqlText = "SELECT [build] "
                           + "FROM [fangyuan] "
                           + "GROUP BY [build]";

            List<string> list = new List<string>();
            OleDbDataReader oleDbDataReader = SFL.OleDbHelper.ExecuteReader(sqlText, null);
            while (oleDbDataReader.Read())
            {
                if (oleDbDataReader["build"] != DBNull.Value)
                    list.Add(oleDbDataReader["build"].ToString());
            }
            oleDbDataReader.Close();
            return list;
        }
        public override List<string> GetUnitList(string build)
        {
            string sqlText = "SELECT [unit] "
                           + "FROM [fangyuan] "
                           + "WHERE [build]=@build GROUP BY [unit] ORDER BY[unit] ASC";
            OleDbParameter[] parameters = 
            {
                new OleDbParameter("@build" , OleDbType.BSTR , 50){ Value = build }
            };

            List<string> list = new List<string>();
            OleDbDataReader oleDbDataReader = SFL.OleDbHelper.ExecuteReader(sqlText, parameters);
            while (oleDbDataReader.Read())
            {
                if (oleDbDataReader["unit"] != DBNull.Value && oleDbDataReader["unit"].ToString().Length > 0)
                    list.Add(oleDbDataReader["unit"].ToString());
            }
            oleDbDataReader.Close();
            return list;
        }
        public override List<string> GetFloorList(string build, string unit)
        {
            string sqlText = "SELECT [floor] "
                           + "FROM [fangyuan] "
                           + "WHERE [build]=@build AND [unit]=@unit GROUP BY [floor] ORDER BY[floor] DESC";
            OleDbParameter[] parameters = 
            {
                new OleDbParameter("@build" , OleDbType.BSTR , 50){ Value = build },
                new OleDbParameter("@unit" , OleDbType.BSTR , 50){ Value = unit }
            };

            List<string> list = new List<string>();
            OleDbDataReader oleDbDataReader = SFL.OleDbHelper.ExecuteReader(sqlText, parameters);
            while (oleDbDataReader.Read())
            {
                if (oleDbDataReader["floor"] != DBNull.Value)
                    list.Add(oleDbDataReader["floor"].ToString());
            }
            oleDbDataReader.Close();
            return list;
        }

        public override List<string> GetTypeList()
        {
            string sqlText = "SELECT [type] "
                           + "FROM [fangyuan] "
                           + "GROUP BY [type] ORDER BY[type] ASC";

            List<string> list = new List<string>();
            OleDbDataReader oleDbDataReader = SFL.OleDbHelper.ExecuteReader(sqlText, null);
            while (oleDbDataReader.Read())
            {
                if (oleDbDataReader["type"] != DBNull.Value)
                    list.Add(oleDbDataReader["type"].ToString());
            }
            oleDbDataReader.Close();
            return list;
        }
#endregion

        public override List<Fangyuan> GetDataByBuild(string build)
        {
            string sqlText = "SELECT * "
                           + "FROM [fangyuan] "
                           + "WHERE [build]=@build";
            OleDbParameter[] parameters = 
            {
                new OleDbParameter("@build" , OleDbType.BSTR , 50){ Value = build }
            };

            List<Fangyuan> list = new List<Fangyuan>();
            OleDbDataReader oleDbDataReader = SFL.OleDbHelper.ExecuteReader(sqlText, parameters);
            while (oleDbDataReader.Read())
            {
                Fangyuan fangyuan = new Fangyuan();
                ReadFangyuanAllData(oleDbDataReader, fangyuan);
                list.Add(fangyuan);
            }
            oleDbDataReader.Close();
            return list;
        }


        /// <summary>
        /// 从数据库中读取并返回所有回迁协议（Huiqianxieyi）List列表。
        /// </summary>
        public override List<Fangyuan> GetAllList()
        {
            string sqlText = "SELECT * "
                           + "FROM [fangyuan]";
            List<Fangyuan> list = new List<Fangyuan>();
            OleDbDataReader oleDbDataReader = SFL.OleDbHelper.ExecuteReader(sqlText, null);
            while (oleDbDataReader.Read())
            {
                Fangyuan fangyuan = new Fangyuan();
                ReadFangyuanAllData(oleDbDataReader, fangyuan);
                list.Add(fangyuan);
            }
            oleDbDataReader.Close();
            return list;
        }


        /// <summary>
        /// 根据每页记录数及所要获取的页数，从数据库中读取并返回经过分页后的回迁协议（Huiqianxieyi）的列表及分页信息。
        /// 该方法所获取的回迁协议（Huiqianxieyi）列表仅用于在数据控件中显示，该方法只为对象中需要显示的属性进行赋值。
        /// </summary>
        public override PageData GetPageList(int pageSize, int curPage)
        {
            string sqlText = "SELECT * "
                           + "FROM [fangyuan]";
            List<Fangyuan> list = new List<Fangyuan>();
            OleDbDataReader oleDbDataReader = SFL.OleDbHelper.ExecuteReader(sqlText, null);

            PageData pageData = new PageData();
            pageData.PageSize = pageSize;
            pageData.CurPage = curPage;
            pageData.RecordCount = 0;
            pageData.PageCount = 1;

            int first = (curPage - 1) * pageSize + 1;
            int last = curPage * pageSize;

            while (oleDbDataReader.Read())
            {
                pageData.RecordCount++;
                if (pageData.RecordCount >= first && last >= pageData.RecordCount)
                {
                    Fangyuan fangyuan = new Fangyuan();
                    ReadFangyuanPageData(oleDbDataReader, fangyuan);
                    list.Add(fangyuan);
                }
            }
            oleDbDataReader.Close();

            if(pageData.RecordCount>0)
                pageData.PageCount = Convert.ToInt32(Math.Ceiling((double)pageData.RecordCount / (double)pageSize));

            pageData.PageList = list;
            return pageData;
        }

        #endregion EasyCode所生成的默认代码

        //﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉
        //  说明：以下区域的代码为设计开发人员所编写，主要为扩展该数据访问类的功能，而定义的变量、属性及相关数据访问方法。  
        //  注意：为了保证该项目的多数据库支持与扩展性，请先在本类的父类中对相关抽象方法进行定义，再在本类中进行具体实现。
        //﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍
        
        public override int? CreateXieyiRelation(int fangyuanindex, int ownerindex)
        {
            Fangyuan fang = GetDataByIndex(fangyuanindex);
            if (fang == null)
                return null;

            //检查重复记录

            int? index = SFL.OleDbHelper.GetMaxIndex("owner") + 1;
            string sqlText = "INSERT INTO [owner]"
                           + "([index],[fangyuanindex],[huiqianhuindex])"
                           + "VALUES"
                           + "(@index,@fangyuanindex,@huiqianhuindex)";
            OleDbParameter[] parameters = 
                {
                    new OleDbParameter("@index"     , OleDbType.Integer , 4){ Value = index },
                    new OleDbParameter("@fangyuanindex"     , OleDbType.Integer , 4){ Value = fangyuanindex },
                    new OleDbParameter("@huiqianhuindex"     , OleDbType.Integer , 4){ Value = ownerindex }
                };
            SFL.OleDbHelper.ExecuteNonQuery(sqlText, parameters);
            return index;
        }
        public override int? DeleteXieyiRelation(int fangyuanindex, int ownerindex)
        {
            string sqlText = "DELETE FROM [owner] "
                           + "WHERE [fangyuanindex]=@fangyuanindex AND [huiqianhuindex]=@huiqianhuindex";
            OleDbParameter[] parameters = 
                {
                    new OleDbParameter("@fangyuanindex" , OleDbType.Integer , 4){ Value = fangyuanindex },
                    new OleDbParameter("@huiqianhuindex" , OleDbType.Integer , 4){ Value = ownerindex }
                };
            return SFL.OleDbHelper.ExecuteNonQuery(sqlText, parameters);
        }

        public override bool IsHuiqianhuExist(int index)
        {
            string sqlText = "SELECT * "
                           + "FROM [owner] "
                           + "WHERE [huiqianhuindex]=@index";
            OleDbParameter[] parameters = 
            {
                new OleDbParameter("@index" , OleDbType.Integer , 4){ Value = index }
            };

            List<string> list = new List<string>();
            OleDbDataReader oleDbDataReader = SFL.OleDbHelper.ExecuteReader(sqlText, parameters);
            return oleDbDataReader.Read();
        }
    }
}
